﻿using System;
using System.Collections.Generic;
using System.Net;
using System.Net.Sockets;
using System.Data;
using System.Data.OleDb;
using System.Data.SQLite;
using System.IO;
using System.Text;
using System.Threading;
using System.Xml;
using System.Collections;
public class NetTableBridge
{
    static Socket CreateSocket(IPAddress ipaddress, int port)
    {
        Socket s = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);
        s.Blocking = true;
        s.Bind(new IPEndPoint(ipaddress, port));
        return s;
    }

    class SerializeIOWriter : TextWriter
    {
        public StreamWriter baseStream;

        public override Encoding Encoding
        {
            get
            {
                return baseStream.Encoding;
            }
        }

        public SerializeIOWriter(StreamWriter sw)
        {
            baseStream = sw;
        }

        #region Write

        public override void Write(char value)
        {
            baseStream.Write(value);
        }

        public override void Write(bool value)
        {
            baseStream.Write(value);
        }

        public override void Write(char[] buffer)
        {
            baseStream.Write(buffer);
        }

        public override void Write(char[] buffer, int index, int count)
        {
            baseStream.Write(buffer, index, count);
        }

        public override void Write(decimal value)
        {
            baseStream.Write(value);
        }

        public override void Write(double value)
        {
            baseStream.Write(value);
        }

        public override void Write(float value)
        {
            baseStream.Write(value);
        }

        public override void Write(int value)
        {
            baseStream.Write(value);
        }

        public override void Write(long value)
        {
            baseStream.Write(value);
        }

        public override void Write(object value)
        {
            baseStream.Write(value);
        }

        public override void Write(string format, object arg0)
        {
            baseStream.Write(format, arg0);
        }

        public override void Write(string format, object arg0, object arg1)
        {
            baseStream.Write(format, arg0, arg1);
        }

        public override void Write(string format, object arg0, object arg1, object arg2)
        {
            baseStream.Write(format, arg0, arg1, arg2);
        }

        public override void Write(string format, params object[] arg)
        {
            baseStream.Write(format, arg);
        }

        public override void Write(string value)
        {
            baseStream.Write(value);
        }

        public override void Write(uint value)
        {
            baseStream.Write(value);
        }

        public override void Write(ulong value)
        {
            baseStream.Write(value);
        }

        #endregion

        #region WriteLine

        public override void WriteLine()
        {
            baseStream.WriteLine();
        }

        public override void WriteLine(bool value)
        {
            baseStream.WriteLine(value);
        }

        public override void WriteLine(char value)
        {
            baseStream.WriteLine(value);
        }

        public override void WriteLine(char[] buffer)
        {
            baseStream.WriteLine(buffer);
        }

        public override void WriteLine(char[] buffer, int index, int count)
        {
            baseStream.WriteLine(buffer, index, count);
        }

        public override void WriteLine(decimal value)
        {
            baseStream.WriteLine(value);
        }

        public override void WriteLine(double value)
        {
            baseStream.WriteLine(value);
        }

        public override void WriteLine(float value)
        {
            baseStream.WriteLine(value);
        }

        public override void WriteLine(int value)
        {
            baseStream.WriteLine(value);
        }

        public override void WriteLine(long value)
        {
            baseStream.WriteLine(value);
        }

        public override void WriteLine(object value)
        {
            baseStream.WriteLine(value);
        }

        public override void WriteLine(string format, object arg0)
        {
            baseStream.WriteLine(format, arg0);
        }

        public override void WriteLine(string format, object arg0, object arg1)
        {
            baseStream.WriteLine(format, arg0, arg1);
        }

        public override void WriteLine(string format, object arg0, object arg1, object arg2)
        {
            baseStream.WriteLine(format, arg0, arg1, arg2);
        }

        public override void WriteLine(string format, params object[] arg)
        {
            baseStream.WriteLine(format, arg);
        }

        public override void WriteLine(string value)
        {
            baseStream.WriteLine(value);
        }

        public override void WriteLine(uint value)
        {
            baseStream.WriteLine(value);
        }

        public override void WriteLine(ulong value)
        {
            baseStream.WriteLine(value);
        }

        #endregion

        public override void Flush()
        {
            baseStream.Flush();
        }

        public override void Close()
        {
            baseStream.Close();
        }

        protected override void Dispose(bool disposing)
        {
            baseStream.Dispose();
            if (disposing)
            {
                baseStream = null;
            }
        }

        public override IFormatProvider FormatProvider
        {
            get
            {
                return baseStream.FormatProvider;
            }
        }

        public override string NewLine
        {
            get
            {
                return baseStream.NewLine;
            }
            set
            {
                baseStream.NewLine = value;
            }
        }
    }

    const string TABLE = "table";
    const string TABLENAME = "name";
    const string TABLEROW = "tr";
    const string TABLEDATA = "td";
    const string TABLECOLUMN = "name";

    enum SQLCOMMANDTYPE : byte
    {
        None = 0,
        Excute = 1,
        List = 2,
    }

    static DataTable DeSerialize(StreamReader sr)
    {
        XmlTextReader xtr = new XmlTextReader(sr);

        DataTable dt = null;
        if (xtr.Read())
        {
            if ((xtr.NodeType == XmlNodeType.Element || xtr.NodeType == XmlNodeType.Document) && xtr.Name == TABLE)
            {
                dt = new DataTable();
                for (int i = 0; i < xtr.AttributeCount; i++)
                {
                    xtr.MoveToAttribute(i);
                    if (xtr.Name == TABLENAME)
                    {
                        dt.TableName = xtr.Value;
                        break;
                    }
                }
                if (DeSerializeTable(dt, xtr))
                    return dt;
                else
                    return null;
            }
        }

        return dt;
    }

    static bool DeSerializeTable(DataTable dt, XmlTextReader xtr)
    {
        ArrayList al = new ArrayList();
        if (xtr.Read() && xtr.NodeType == XmlNodeType.Element && xtr.Name == TABLEROW)//DataColumnsInitial
        {
            DataColumn dc = null;
            while (xtr.Read())
            {
                if (xtr.NodeType == XmlNodeType.Element && xtr.Name == TABLEDATA)
                {
                    if (dc == null)
                        dc = new DataColumn();
                    for (int i = 0; i < xtr.AttributeCount; i++)
                    {
                        xtr.MoveToAttribute(i);
                        if (xtr.Name == TABLECOLUMN)
                        {
                            try
                            {
                                dc.ColumnName = xtr.Value;
                            }
                            catch (Exception)
                            {
                                return false;
                            }
                            break;
                        }
                    }
                    if (!xtr.Read())
                        return false;
                    if (xtr.NodeType == XmlNodeType.Text)
                        dc.DataType = Type.GetType(xtr.Value);
                    if (!xtr.Read())
                        return false;
                    if (xtr.NodeType != XmlNodeType.EndElement || xtr.Name != TABLEDATA)
                        return false;
                    else
                    {
                        dt.Columns.Add(dc);
                        dc = null;
                    }
                }
                else if (xtr.NodeType == XmlNodeType.EndElement && xtr.Name == TABLEROW)//CompleteDataColumnsInitial
                {
                    break;
                }
            }
            DataRow dr = null; int DataColumnIndex = 0; Type DataColumnType;
            int ReadContentAsInt; long ReadContentAsLong; float ReadContentAsFloat; bool ReadContentAsBoolean; DateTime ReadContentAsDateTime;
            while (xtr.Read())
            {
                switch (xtr.NodeType)
                {
                    case XmlNodeType.Element:
                        if (xtr.Name == TABLEROW) { dr = dt.NewRow(); DataColumnIndex = 0; }
                        else if (xtr.Name == TABLEDATA) { if (xtr.IsEmptyElement)DataColumnIndex++; continue; }
                        break;
                    case XmlNodeType.EndElement:
                        if (xtr.Name == TABLEROW) { dt.Rows.Add(dr); }
                        else if (xtr.Name == TABLEDATA) { DataColumnIndex++; }
                        else if (xtr.Name == TABLE) { return true; }
                        break;
                    case XmlNodeType.Text:
                        DataColumnType = dt.Columns[DataColumnIndex].DataType;
                        if (DataColumnType == typeof(int))
                        {
                            if (int.TryParse(xtr.Value, out ReadContentAsInt)) { dr[DataColumnIndex] = ReadContentAsInt; } else { return false; }
                        }
                        else if (DataColumnType == typeof(long))
                        {
                            if (long.TryParse(xtr.Value, out ReadContentAsLong)) { dr[DataColumnIndex] = ReadContentAsLong; } else { return false; }
                        }
                        else if (DataColumnType == typeof(float))
                        {
                            if (float.TryParse(xtr.Value, out ReadContentAsFloat)) { dr[DataColumnIndex] = ReadContentAsFloat; } else { return false; }
                        }
                        else if (DataColumnType == typeof(bool))
                        {
                            if (bool.TryParse(xtr.Value, out ReadContentAsBoolean)) { dr[DataColumnIndex] = ReadContentAsBoolean; } else { return false; }
                        }
                        else if (DataColumnType == typeof(DateTime))
                        {
                            if (DateTime.TryParse(xtr.Value, out ReadContentAsDateTime)) { dr[DataColumnIndex] = ReadContentAsDateTime; } else { return false; }
                        }
                        else
                        {
                            dr[DataColumnIndex] = xtr.Value;
                        }
                        break;
                }
            }
        }
        else
        {
            return false;
        }
        return false;
    }

    static bool DeSerializeTableColumns(DataTable dt, XmlTextReader xtr)
    {
        return true;
    }

    static void EnSerialie(StreamWriter sw, DataTable dt)
    {
        StringBuilder sb = new StringBuilder();
        XmlTextWriter xtw = new XmlTextWriter(new SerializeIOWriter(sw));
        if (dt == null)
        {
            xtw.Close();
            return;
        }
        xtw.Formatting = Formatting.None;

        xtw.WriteStartElement(TABLE);
        if (!string.IsNullOrEmpty(dt.TableName))
        {
            xtw.WriteStartAttribute(TABLENAME); xtw.WriteString(dt.TableName);
        }

        #region Header

        if (dt.Columns.Count > 0)
            xtw.WriteStartElement(TABLEROW);

        foreach (DataColumn i in dt.Columns)
        {
            xtw.WriteStartElement(TABLEDATA);
            xtw.WriteStartAttribute(TABLECOLUMN);
            xtw.WriteString(i.ColumnName);
            xtw.WriteEndAttribute();
            xtw.WriteString(i.DataType.FullName);
            xtw.WriteEndElement();
        }
        if (dt.Columns.Count > 0)
            xtw.WriteEndElement();

        #endregion

        #region Body

        foreach (DataRow i in dt.Rows)
        {
            xtw.WriteStartElement(TABLEROW);

            foreach (object j in i.ItemArray)
            {
                xtw.WriteStartElement(TABLEDATA);
                if (j != DBNull.Value)
                    xtw.WriteString(j.ToString());
                xtw.WriteEndElement();
            }

            xtw.WriteEndElement();
        }

        #endregion

        xtw.WriteEndElement();
        xtw.Flush();
    }

    public class Server
    {
        public Server(int Port)
        {
            Socket s = CreateSocket(IPAddress.Any, Port);
            s.Listen(3);
            while (true)
            {
                ThreadPool.QueueUserWorkItem(new WaitCallback(ThreadItems), s.Accept());
            }
        }

        void ThreadItems(object SenderSockect)
        {
            Socket s = SenderSockect as Socket;
            if (s == null)
                return;
            try
            {
                NetworkStream ns = new NetworkStream(s);
                StreamReader sr = new StreamReader(ns, Encoding.UTF8);
                StreamWriter sw = new StreamWriter(ns, Encoding.UTF8);
                string sql = sr.ReadLine();
                while (!string.IsNullOrEmpty(sql))
                {
                    Excute(sql, sw);
                    sql = sr.ReadLine();
                }
                s.Close();
                s = null;
            }
            catch (Exception)
            {
            }
        }

        void Excute(string sql, StreamWriter sw)
        {
            DataTable dt = null;
            if (sql == null)
            {
                EnSerialie(sw, dt);
            }
            try
            {
                if (sql.StartsWith("L"))
                {
                    dt = new DiagnosisDrugs { Status = 1 }.List();
                }
                else if (sql.StartsWith("A"))
                {
                    dt = new DataTable
                    {
                        TableName = "ExcuteResult",
                    };
                    dt.Columns.Add(new DataColumn { DataType = typeof(int), ColumnName = "Result" });
                    DataRow dr = dt.NewRow();
                    dr[0] = DB.getInstance().Affected(sql.Substring(1));
                    dt.Rows.Add(dr);
                }
            }
            catch (Exception)
            {
            }
            EnSerialie(sw, dt);
        }
    }

    public class Client
    {
        StreamWriter sw = null;
        StreamReader sr = null;

        #region New

        public Client(long IP, int Port)
        {
            Socket s = CreateSocket(IPAddress.Any, new Random().Next(1000, 2000));
            try
            {
                s.Connect(new IPAddress(IP), Port);
                NetworkStream ns = new NetworkStream(s);
                sw = new StreamWriter(ns, Encoding.UTF8);
                sr = new StreamReader(ns, Encoding.UTF8);
            }
            catch (Exception ex)
            {

            }
        }

        public Client(byte[] IP, int Port)
        {
            Socket s = CreateSocket(IPAddress.Any, new Random().Next(1000, 2000));
            try
            {
                s.Connect(new IPAddress(IP), Port);
                NetworkStream ns = new NetworkStream(s);
                sw = new StreamWriter(ns, Encoding.UTF8);
                sr = new StreamReader(ns, Encoding.UTF8);
            }
            catch (Exception ex)
            {

            }
        }

        ~Client()
        {
            if (sw != null)
            {
                sw.Close();
            }
        }

        #endregion

        DataTable ExcuteSQL(string SQL, SQLCOMMANDTYPE Type)
        {
            if (sw == null || sr == null)
                return null;
            switch (Type)
            {
                case SQLCOMMANDTYPE.List:
                    sw.Write('L'); break;
                case SQLCOMMANDTYPE.Excute:
                    sw.Write('E'); break;
                default:
                    sw.Write('N'); break;
            }
            sw.WriteLine(SQL);
            sw.Flush();
            try
            {
                return DeSerialize(sr);
            }
            catch (Exception)
            {
                return null;
            }
        }

        public DataTable ExcuteDataTable(string SQL)
        {
            return ExcuteSQL(SQL, SQLCOMMANDTYPE.List);
        }

        public DataTable ExcuteAffect(string SQL)
        {
            return ExcuteSQL(SQL, SQLCOMMANDTYPE.Excute);
        }
    }
}